package main

import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	"log"
	"net/http"
	"strconv"

	_ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initializeDatabase() (err error) {
	//data source name
	//用户名@密码@协议(ip:port)/数据库
	dsn := "root:123456@tcp(gincloud:3306)/gin_demo?charset=utf8mb4&parseTime=True&loc=Local"
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return
	}

	//资源释放
	//defer db.Close()

	//尝试建立连接
	if err = db.Ping(); err != nil {
		fmt.Println("connect 2 database failed...")
		return
	}
	//最大连接数
	db.SetMaxOpenConns(10)
	//最大闲置连接数
	db.SetMaxIdleConns(10)
	return nil
}

type user struct {
	//如果需要返回前端, 那么首字母需要大写(小写为私有)
	//前端展示效果可以通过 `json:"id"` 来处理
	Id   uint64 `json:"id"`
	Name string `json:"name"`
	Age  uint32 `json:"age"`
}

func querySingleRow(id uint64) user {
	var u user
	sqlStr := "select * from student where id = ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Printf("scan failed err : %v\n", err)
	}
	//资源释放
	defer stmt.Close()
	if err = stmt.QueryRow(id).Scan(&u.Id, &u.Name, &u.Age); err != nil {
		log.Printf("scan failed err : %v\n", err)
	}

	log.Println(u.Id, u.Name, u.Age)
	return u
}

func queryMultiRow() []user {
	sqlStr := "select * from student where id >= ?"
	//编译模板语句
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Printf("scan failed err : %v\n", err)
	}
	//资源释放
	defer stmt.Close()

	//传递数据
	rows, _ := stmt.Query(5)
	defer rows.Close()

	users := make([]user, 0)
	for rows.Next() {
		var u user
		err = rows.Scan(&u.Id, &u.Name, &u.Age)
		if err != nil {
			log.Println(err)
			return nil
		}
		//log.Println(u.Id, u.Name, u.Age)
		users = append(users, u)
	}

	return users
}

func updateRow() {
	sqlStr := "update student set name=? where id = ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Printf("scan failed err : %v\n", err)
	}
	//资源释放
	defer stmt.Close()
	res, err := stmt.Exec("gin", 1)
	if err != nil {
		log.Printf("update failed err : %v\n", err)
	}
	affectedNum, err := res.RowsAffected()
	if err != nil {
		log.Printf("update RowsAffected failed err : %v\n", err)
	}
	log.Println("受影响的行数=", affectedNum)
}

func deleteRow() {
	sqlStr := "delete from student where id = ?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Printf("scan failed err : %v\n", err)
	}
	//资源释放
	defer stmt.Close()
	res, err := stmt.Exec(6)
	if err != nil {
		log.Printf("update failed err : %v\n", err)
	}
	affectedNum, err := res.RowsAffected()
	if err != nil {
		log.Printf("update RowsAffected failed err : %v\n", err)
	}
	log.Println("受影响的行数=", affectedNum)
}

func insertRow() {
	sqlStr := "insert into student (name, age) values (?,?)"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Printf("insert failed err : %v\n", err)
	}
	defer stmt.Close()

	//可以同时执行多条
	//数据1
	res, err := stmt.Exec("诺诺", 20)
	id, err := res.LastInsertId()
	if err != nil {
		log.Printf("insert LastInsertId failed err : %v\n", err)
	}
	log.Println("上次插入的id=", id)

	//数据2
	res2, err2 := stmt.Exec("荀彧", 20)
	id2, err2 := res2.LastInsertId()
	if err != nil {
		log.Printf("insert LastInsertId failed err : %v\n", err2)
	}
	log.Println("上次插入的id=", id2)
}

//预编译方式, 先传递sql给mysql生成模板, 再传递参数
func main() {
	if err := initializeDatabase(); err != nil {
		panic(err)
	}
	fmt.Println("connected")

	r := gin.Default()

	r.GET("/user/:id", func(c *gin.Context) {
		idStr := c.Param("id")
		var id, _ = strconv.ParseInt(idStr, 10, 64)
		fmt.Println(id)
		u := querySingleRow((uint64)(id))
		c.JSON(http.StatusOK, gin.H{
			"id":   id,
			"user": u,
		})
	})

	r.GET("/user", func(c *gin.Context) {
		u := queryMultiRow()
		c.JSON(http.StatusOK, gin.H{
			"user": u,
		})
	})

	r.GET("/user/update", func(c *gin.Context) {
		updateRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.GET("/user/delete", func(c *gin.Context) {
		deleteRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.GET("/user/insert", func(c *gin.Context) {
		insertRow()
		c.JSON(http.StatusOK, gin.H{
			"msg": "ok",
		})
	})

	r.Run(":8080")
}
